package alg;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.lang.time.StopWatch;

import data.UserRating;
import data.UserRatingList;
import data.UserSimilarity;

import exception.*;
import data.*;

public class DataManagerMySql extends DataManager {

	private Connection connection;
	private StopWatch sw = new StopWatch();

	private void execute(String sql) throws SQLException {
		connection.prepareStatement(sql).execute();
	}

	public DataManagerMySql() throws DataManagerException {
		DatasourceMySql ds = new DatasourceMySql();
		try {
			this.connection = ds.getConnection();
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public void init() throws DataManagerException {

		try {
			PreparedStatement statement;
			String sql;

			execute("DROP INDEX RatingUserIdIndx ON tbl_ratings;");
			execute("DROP INDEX RatingProfileIdIndx ON tbl_ratings;");

			// LOCK TABLES a WRITE; non serve..
			loadRatings();
			loadUsers();

			if (ConfigManager.verbose)
				System.out.print("Aggiornamento indici, attendi...");

			execute("CREATE INDEX RatingUserIdIndx ON tbl_ratings (UserId) USING HASH;");
			execute("CREATE INDEX RatingProfileIdIndx ON tbl_ratings (ProfileId) USING HASH;");

			if (ConfigManager.verbose)
				System.out.println("fatto!");

		} catch (Exception e) {
			throw new DataManagerException(e);
		}

	}

	public void close() throws DataManagerException {
		try {
			this.connection.close();
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public List<Integer> getAllUsers() throws DataManagerException {
		try {
			List<Integer> userlist = new LinkedList<Integer>();
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			ratings_sql = "SELECT * FROM tbl_users";
			
			statement = connection.prepareStatement(ratings_sql);
			statement.execute();
			rs = statement.getResultSet();
			int i =0;
			while (rs.next()) {
				i++;
				int UserId = rs.getInt("UserId");
				userlist.add(UserId);
			}
			statement.close();
			rs.close();
			return userlist;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}
	
	public void dropUserSimilarityIndex() throws DataManagerException{
		try {
			execute("DROP INDEX RatingUserIdIndx ON tbl_ratings;");
			execute("DROP INDEX RatingProfileIdIndx ON tbl_ratings;");
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}
	
	public void createUserSimilarityIndex() throws DataManagerException{
		try {
			execute("CREATE INDEX PSimilUserIdIndx ON tbl_pcc (UserId) USING HASH;");
			execute("CREATE INDEX PSimilProfileIdIndx ON tbl_pcc (ProfileId) USING HASH;");
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
		
	}

	public void storeUserSimilarities(List<UserSimilarity> similarities)
			throws DataManagerException {
		try {
			String sql = "INSERT INTO tbl_pcc VALUES ";
			String sep = "";
			PreparedStatement statement;
			for (UserSimilarity s : similarities) {
				sql += sep + String.format("(%d,%d,%s )", s.UserId1, s.UserId2,
						String.valueOf(s.similarity).replace(",", "."));
				sep = ",";
			}
			sql +=";";
			statement = connection.prepareStatement(sql);
			statement.executeUpdate();

		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public UserRatingList getRatings(int UserId) throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			UserRatingList ratings = new UserRatingList();

			ratings_sql = "SELECT distinct ProfileId,Rating FROM tbl_ratings WHERE UserId=? ORDER BY ProfileID";
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, UserId);
			statement.execute();
			rs = statement.getResultSet();

			while (rs.next()) {
				UserRating rating = new UserRating();
				rating.UserId = rs.getInt("ProfileId");
				rating.Rating = rs.getInt("Rating");
				ratings.add(rating);
			}

			statement.close();
			rs.close();
			return ratings;

		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public UserRatingList getScores(int UserId) throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			String ratings_sql;
			UserRatingList ratings = new UserRatingList();

			ratings_sql = "SELECT UserId,Rating FROM tbl_ratings WHERE ProfileId=? ORDER BY ProfileID";
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, UserId);
			statement.execute();
			rs = statement.getResultSet();

			while (rs.next()) {
				UserRating score = new UserRating();
				score.Rating = rs.getInt("Rating");
				score.UserId = rs.getInt("UserId");

				ratings.add(score);
			}

			statement.close();
			rs.close();
			return ratings;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public int getRating(int UserId, int ProfileId) throws DataManagerException {
		try {
			PreparedStatement statement;
			ResultSet rs;
			int rating = (Integer) null;
			String ratings_sql;

			ratings_sql = "SELECT * FROM tbl_ratings WHERE UserId=? AND ProfileId=?";
			statement = connection.prepareStatement(ratings_sql);
			statement.setInt(1, ProfileId);
			statement.execute();
			rs = statement.getResultSet();

			if (rs.next()) {
				rating = rs.getInt("Rating");
			}
			statement.close();
			rs.close();
			return rating;
		} catch (Exception e) {
			throw new DataManagerException(e);
		}
	}

	public void loadRatings() throws ConfigurationException, SQLException {

		String insert_ratings_sql;
		String ratings_path;
		ConfigManager config;
		File current_dir;
		PreparedStatement statement = null;

		if (ConfigManager.verbose)
			System.out.print("Inserimento dei rating nel db, attendi...");
		config = ConfigManager.getInstance();
		current_dir = new File("");
		ratings_path = current_dir.getAbsolutePath().toString()
				+ System.getProperty("file.separator")
				+ config.get("data.ratings");
		if (System.getProperty("file.separator").equals("\\"))
			ratings_path = ratings_path.replace("\\", "\\\\");

		insert_ratings_sql = String
				.format("LOAD DATA LOCAL INFILE '%s' INTO TABLE %s.tbl_ratings FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@col1,@col2,@col3) SET UserID=@col1,ProfileID=@col2,Rating=@col3;",
						ratings_path, config.get("db.schema"));

		try {
			statement = this.connection.prepareStatement(insert_ratings_sql);
			statement.setQueryTimeout(60 * 10);
			statement.execute();
			if (ConfigManager.verbose)
				System.out.println("fatto!");
		} catch (SQLException e) {
			throw e;
		} finally {
			statement.close();
		}

	}

	public void loadUsers() throws ConfigurationException, SQLException {

		String insert_users_sql;
		String users_path;
		ConfigManager config;
		File current_dir;
		PreparedStatement statement = null;

		if (ConfigManager.verbose)
			System.out.print("Inserimento dei profili nel db, attendi...");
		config = ConfigManager.getInstance();
		current_dir = new File("");
		users_path = current_dir.getAbsolutePath().toString()
				+ System.getProperty("file.separator")
				+ config.get("data.ratings");
		if (System.getProperty("file.separator").equals("\\"))
			users_path = users_path.replace("\\", "\\\\");

		insert_users_sql = String
				.format("LOAD DATA LOCAL INFILE '%s' INTO TABLE %s.tbl_users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@col1,@col2) SET UserID=@col1,Gender=@col2;",
						users_path, config.get("db.schema"));
		try {
			statement = this.connection.prepareStatement(insert_users_sql);
			statement.setQueryTimeout(60 * 10);
			statement.execute();
			if (ConfigManager.verbose)
				System.out.println("fatto!");
		} catch (SQLException e) {
			throw e;
		} finally {
			statement.close();
		}

	}
}
